<?php

$dbhost = "localhost";
$dbuser = "root";
$dbpassword = "";

$database = "ThreadExtraction";
$dataTable = "Data";
$annotationsTable = "Annotations";
$fileTable = "Files";

$page = $_GET['page']; // get the requested page 
$limit = $_GET['rows']; // get how many rows we want to have into the grid 
$sidx = $_GET['sidx']; // get index row - i.e. user click to sort 
$sord = $_GET['sord']; // get the direction 
$filenumber = $_GET['filenumber'];
$annotator1 = $_GET['annotator1'];
$annotator2 = $_GET['annotator2'];

if(!$sidx) {
	$sidx =1; 
	$sord = "asc";
}
// connect to the database 
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error()); 
mysql_select_db($database) or die("Error conecting to db."); 

$result = mysql_query("select id from Files where name='$filename'");
if(!$result) {
  die("Invalid query" . "select id from Files where name='$filename'");
}
$row = mysql_fetch_array($result, MYSQL_ASSOC);
#$filenumber = $row['id'];

$result = mysql_query("SELECT COUNT(*) AS count FROM Data where filenumber=$filenumber"); 
$row = mysql_fetch_array($result,MYSQL_ASSOC); 
$count = $row['count']; 

if( $count >0 && $limit > 0) { 
	$total_pages = ceil($count/$limit); 
} else { 
	$total_pages = 0; 
} 

if ($page > $total_pages) 
	$page=$total_pages; 

$start = $limit*$page - $limit; 

// do not put $limit*($page - 1) 
$dataSql = "select postnum as postid, system, timestamp, user, content from data where filenumber=$filenumber ORDER BY $sidx";

$annotationsSql = "select p.postnum as postid, a.annotator, a.parent, a.relation, a.thread from annotations a, data p where a.postid=p.id and p.filenumber=$filenumber and (a.annotator = $annotator1 or a.annotator = $annotator2)";

$data = mysql_query($dataSql) or die("Couldn't execute data query.".mysql_error()); 
$annotations = mysql_query($annotationsSql) or die("Couldn't execute annotation query.".mysql_error().$annotationsSql); 

$response->page = $page; $response->total = $total_pages; 
$response->userdata = array("fileno" => $filenumber, "filename" => $filename);
$response->records = $count; 
$i=0; 

while($row = mysql_fetch_array($data, MYSQL_ASSOC)) { 
//        print_r($hash);
//        print_r($row);
        $hash[$row[postid]] = $i;
	$response->rows[$i]['id']=$row[postid];
	$response->rows[$i]['cell']=array($row[postid], $row[timestamp], $row[system], $row[user], $row[content]);
	$i++; 
} 
while($row = mysql_fetch_array($annotations, MYSQL_ASSOC)) {
        if(!isset($annotationArr[$hash[$row[postid]]])) {
		$annotationArr[$hash[$row[postid]]] = Array();
        }
	array_push($annotationArr[$hash[$row[postid]]], $row[parent], $row[relation], $row[thread]);
}

while (list($key, $val) = each($annotationArr)) {
	if(isset($response->rows[$key])) {
	$response->rows[$key]['cell'] = array_merge($response->rows[$key]['cell'], $val);
        }
}
#print_r($response);
echo json_encode($response);
?>
